﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Base;
using System.Data.SqlClient;

namespace Lamex.mMES.UI
{
    public partial class FrmMES_Synchronization :Lamex.Library.frmBaseChild
    {
        List<string> delList = new List<string>();
        List<string> createList = new List<string>();
        string testConnect = @"Data Source={0},1433;Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connection Timeout=30";
        string normConnect = @"Data Source={0},1433;Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connection Timeout=30";
        public FrmMES_Synchronization()
        {
            InitializeComponent();
            this.Load += new EventHandler(FrmMES_Synchronization_Load);
            this.simpleButton1.Click += new EventHandler(simpleButton1_Click);
        }
        private void FrmMES_Synchronization_Load(object sender,EventArgs e)
        {
            try
            {
                string normSql = @"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,TABLE_TYPE   FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE='BASE TABLE'  order by TABLE_NAME";
                testConnect = string.Format(testConnect, "172.23.134.29", "LamexMESManager", "sa", "Sa@1050%");
                normConnect = string.Format(normConnect, "172.23.134.36", "LamexMESManager", "sa", "Sa@1050%");
                DataTable dtTest = GetDataTableBySql(normSql, testConnect);
                DataTable dtNorm = GetDataTableBySql(normSql, normConnect);
                lblNorm.Text = "【172.23.134.36】";
                lblTest.Text = "【172.23.134.29】";

                foreach (DataRow dr in dtTest.Rows)
                {
                    uctestDatabase.Properties.Items.Add(dr["TABLE_NAME"]);
                }
                foreach (DataRow dr in dtNorm.Rows)
                {
                    ucnormDatabase.Properties.Items.Add(dr["TABLE_NAME"]);
                }
                
            }
            catch(Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
            }

        }


        public static DataTable GetDataTableBySql(string sql,string url)
        {
            try
            {
                string sConn = url;
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand()) 
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    CloseConnection(con);
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public static int ExecuteNonQuery(string sql, string url)
        {
            try
            {

                string sConn = SqlConfiguration.CurrentConnectionString;
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand()) //创建命令对象  com = new SqlCommand();
                {
                    cmd.CommandText = sql;
                    int m = cmd.ExecuteNonQuery();
                    CloseConnection(con);
                    return m;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private static void CloseConnection(SqlConnection connection)
        {
            if (connection == null) return;
            if (connection.State != ConnectionState.Closed) connection.Close();
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            if (DialogResult.No == XtraMessageBox.Show("是否要删除表【" + uctestDatabase.Text + "】数据", "", MessageBoxButtons.OKCancel))
                return;
            if(string.IsNullOrEmpty(uctestDatabase.Text))
            {
                XtraMessageBox.Show("表名不能为空！");
                return;
            }
            string deleteSql = " drop table " + uctestDatabase.Text;
           
            List<string> _lst = new List<string>();
            _lst.Add(deleteSql);
            string error = "";
            int m= GetProExecute("p_MES_ExecuteSQL", _lst,testConnect,ref error);
            if (m==2)
            {
                XtraMessageBox.Show("成功删除表！");
            }
            else
            {
                XtraMessageBox.Show(error);
            }
        }


        private void DeleteTable()
        {
            try
            {
                //if (DialogResult.No == XtraMessageBox.Show("是否要删除表【" + uctestDatabase.Text + "】数据", "", MessageBoxButtons.OKCancel))
                //    return;
                if (string.IsNullOrEmpty(uctestDatabase.Text))
                {
                    XtraMessageBox.Show("表名不能为空！");
                    return;
                }
                createList.Clear();
                List<string> _lst = new List<string>();
                DataTable dtForeign = GetDataTable(normConnect, @"select 
object_name(b.parent_object_id) as foreignName
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id 
inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id 
where object_name(B.referenced_object_id)='"+ uctestDatabase.Text + "';");
                string deleteSql = "";
                if(dtForeign.Rows.Count!=0)
                {
                    foreach (DataRow d in dtForeign.Rows)
                    {
                         deleteSql += " drop table " + d["foreignName"].ToString()+";";
                        //_lst.Add(deleteSql);
                        createList.Add(d["foreignName"].ToString());
                    }
                }

                DataTable dt = GetDataTable(normConnect, "select top 1 * from sysObjects where Id=OBJECT_ID('" + uctestDatabase.Text + "') and xtype='U'");
                if(dt.Rows.Count!=0)
                {
                    deleteSql += " drop table " + uctestDatabase.Text+";";
                    //_lst.Add(deleteSql);
                    createList.Add(uctestDatabase.Text);
                }
                _lst.Add(deleteSql);
                string error = "";
                int m = GetProExecute("p_MES_ExecuteSQL", _lst, testConnect, ref error);
                //if (m != 2)
                //{
                //    XtraMessageBox.Show("测试库删表失败！");
                //}
                if(!string.IsNullOrEmpty(error))
                {
                    XtraMessageBox.Show(error);
                }
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
            }
        }
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(uctestDatabase.Text))
                {
                    XtraMessageBox.Show("测式库表名不能为空！");
                    return;
                }
                if (string.IsNullOrEmpty(ucnormDatabase.Text))
                {
                    XtraMessageBox.Show("正式库表名不能为空！");
                    return;
                }
                if (uctestDatabase.Text != ucnormDatabase.Text)
                {
                    XtraMessageBox.Show("表名不一致！");
                    return;
                }
                //if (string.IsNullOrEmpty(ucSortField.Text))
                //{
                //    XtraMessageBox.Show("排序字段不能为空！");
                //    return;
                //}

                DeleteTable();

                foreach (string s in createList)
                {


                    string normSql = " select * from " + s;

                    List<string> lst = new List<string>();
                    List<string> _lst = new List<string>();
                    lst.Add(s);
                    DataTable dt = BaseBuildSql.GetProDataTable("p_mMES_GetAutoTableDescript", lst);//获取表结构
                    if (dt.Rows.Count == 0) return;
                    string desriptioSql = dt.Rows[0]["TableDescription"].ToString().Replace("IDENTITY(1,1)", "");
                    _lst.Add(desriptioSql);

                    string msg = "";
                    string error = "";
                    int m = GetProExecute("p_MES_ExecuteSQL", _lst, testConnect,ref error);
                    if(!string.IsNullOrEmpty(error))
                    {
                        XtraMessageBox.Show(error);
                        return;
                    }
                    if (m == 1)
                    {
                        List<string> lst2 = new List<string>();
                        lst2.Add(s);
                        lst2.Add(ucSortField.Text);
                        DataTable dttemp = BaseBuildSql.GetProDataTable("p_MES_ImportData", lst2);//获取表结构
                        DataTable temp = dttemp.Copy();
                        DataSet ds = new DataSet();
                        temp.TableName = s;
                        ds.Tables.Add(temp);
                        string fieldSql = @" select
                                    a.name as FieldName, --字段名
                                    a.isnullable, --是否可为空
                                    --b.Value as FieldDesc, --字段说明
                                    c.name as FieldType, --数据类型
                                    COLUMNPROPERTY(a.id, a.name, 'IsIdentity') as isidentity, --是否标识列
                                    PK =case when exists(SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = a.id and name in (
                                            SELECT name FROM sysindexes WHERE indid in(
                                            SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
                                    ))) then 'true' else 'false' end--是否主键
                                    from SysColumns a left JOIN
                                    systypes c on a.xusertype = c.xusertype
                                    where a.id = Object_Id('" + s + @"') ";
                        DataTable dtFiled = BaseBuildSql.GetDataTableBySql(fieldSql);
                        string sql = "insert into " + s + "( ";
                        string sql2 = "select ";
                        string field = "";
                        string field2 = "";
                        foreach (DataRow d in dtFiled.Rows)
                        {

                            //if (d["PK"].ToString() == "true")
                            //{
                            //   if(d["isidentity"].ToString()!="1")
                            //    {
                            //        field += d["FieldName"].ToString() + ",";
                            //        field2 += d["FieldName"].ToString() + ",";
                            //    }
                            //}
                            //else
                            //{
                            //    field += d["FieldName"].ToString() + ",";
                            //    field2 += d["FieldName"].ToString() + ",";
                            //}
                            field += d["FieldName"].ToString() + ",";
                            field2 += d["FieldName"].ToString() + ",";
                        }
                        field = field.Trim(',');
                        field2 = field2.Trim(',');
                        sql += field;
                        sql2 += field2;

                        sql += " ) ";
                        sql2 += " from #" + s + "";
                        sql = sql + sql2;
                        if (ExecuteInsertBulkData("select * into #" + s + " from  " + s + " where 1!=1", ds, sql))
                        {
                            
                        }

                    }

                    

                    //XtraMessageBox.Show("数据迁移成功！");

                   

                }
                XtraMessageBox.Show("数据迁移成功！");
            }
             catch(Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
            }
            
        }
        public  bool ExecuteInsertBulkData(string tempSql, DataSet ds, string sql)
        {
            
            string sConn = testConnect;
            SqlConnection con = new SqlConnection(sConn);
            con.Open();
            using (SqlCommand cmd = con.CreateCommand()) //创建命令对象  com = new SqlCommand();
            {

                cmd.CommandText = tempSql;
                int m = cmd.ExecuteNonQuery();
                string name = "";

                foreach (DataTable dt in ds.Tables)
                {
                    name = dt.TableName;
                    string dtTempName = "#" + dt.TableName; //构建需要插入数据的临时表名称
                    SqlBulkCopy sqlBC = new SqlBulkCopy(con);//启用批量复制；
                    sqlBC.BulkCopyTimeout = 5000;
                    sqlBC.DestinationTableName = dtTempName;
                    sqlBC.NotifyAfter = 100;
                    sqlBC.WriteToServer(dt);

                }
                //************************开始事务操作数据*****************************//
                SqlCommand command = con.CreateCommand();
                SqlTransaction transaction;
                transaction = con.BeginTransaction("SampleTransaction");//开始事务
                command.Connection = con;
                command.Transaction = transaction;
                try
                {
                    string tranSql = "set identity_insert " + name + " on  ;"+ sql+ " ; set identity_insert " + name + " off  ;";
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                    transaction.Commit();//提交事务
                    con.Close();
                    return true;
                }
                catch (System.Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }
                finally
                {
                    con.Close();
                }

            }

        }
        public  int GetProExecute(string storeProcName, List<string> strArray, string database ,ref string error)
        {
            try
            {
                string sConn = database;
                
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                SqlCommand cmd =RecComand(storeProcName, strArray, con,database);
                int m= cmd.ExecuteNonQuery();
                CloseConnection(con);
                return (int)cmd.Parameters["@return"].Value; 
                    
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return 0;
            }
        }
        public  SqlCommand RecComand(string proName, List<string> strArray, SqlConnection con,string database)
        {
            SqlCommand cmd = new SqlCommand(proName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            DataTable dt =GetProcedurePara(proName, database);//获取存储过程参数列表
            return AutoAddParameter.AddCommandPara(dt, cmd, strArray);
        }
        public static DataTable GetProcedurePara(string proName, string database)
        {
            try
            {
                string sConn = database;
                string sql = @"select name,id,type,colstat,length from syscolumns where ID in    
                          (SELECT id FROM sysobjects as a  
                           WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1  and id = object_id(N'" + proName + "'))";
                SqlConnection con = new SqlConnection(sConn);
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {

                return null;
            }
        }
        private void getList()
        {
            string connstr1 = testConnect;
            string connstr = testConnect;

            string select = "select a.* from (select * from KnowledgeData1..sysobjects where xtype in ('FN','TF','FR','V','P'))a left join ";
            select += "(select * from KnowledgeData..sysobjects where xtype in ('FN','TF','FR','V','P')) b ";
            select += "on a.name = b.name and a.xtype = b.xtype where b.name is null order by xtype, name";
            System.Data.DataTable dtObjects = GetDataTable(connstr1, select);

            foreach (System.Data.DataRow dr in dtObjects.Rows)
            {
                try
                {
                    string ObjectName = dr["name"].ToString();
                    Console.WriteLine("Process " + ObjectName + "-------------------------------------------");
                    StringBuilder sb = GetObjectsCreateCode(dr["name"].ToString());
                    Console.WriteLine(sb.ToString());
                    ExecuteCmd(connstr, sb.ToString());
                    Console.WriteLine("Process " + ObjectName + " successfully...");
                    System.Threading.Thread.Sleep(1000);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    continue;
                }
            }
        }
        private System.Data.DataTable GetDataTable(string connstr, string select)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connstr);
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(select, conn);
            System.Data.DataTable dt = new System.Data.DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        private void ExecuteCmd(string connstr, string cmd)
        {
            //System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connstr);
            //System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(cmd, conn);
            //conn.Open();
            //comm.ExecuteNonQuery();
            //conn.Close();
        }

        private StringBuilder GetObjectsCreateCode(string ObjectName)
        {
            StringBuilder sb = new StringBuilder();
            //string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["KnowledgeData1"].ConnectionString;
            //string spname = string.Format("sp_helptext {0}", ObjectName);
            //System.Data.DataTable dtHelpText = GetDataTable(connstr, spname);
            //foreach (System.Data.DataRow dr in dtHelpText.Rows)
            //{
               
            //    sb.AppendLine(dr[0].ToString());
            //}
            return sb;
        }

        private void uctestDatabase_UcTextChanged(object sender, EventArgs e)
        {
            
        }

        private void ucnormDatabase_UcTextChanged(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(ucnormDatabase.Text))
            {
                XtraMessageBox.Show("正式库不能为空");
                return;
            }
            uctestDatabase.Text = ucnormDatabase.Text;
            ucSortField.Properties.Items.Clear();
            DataTable dt = BaseBuildSql.GetSysColunmNameList(ucnormDatabase.Text);
            foreach (DataRow dr in dt.Rows)
            {
                ucSortField.Properties.Items.Add(dr["column_name"].ToString());
            }
        }

        
    }
}